using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Xml;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using Workflows.Components;

namespace Workflows.Components.DB.Sql
{
    /// <summary></summary>
    [Serializable] 
    public class SqlInstruction : InstructionBase
    {
        #region prop
        private string _ConnStr;
        /// <summary></summary>
        public string ConnStr
        {
            get { return _ConnStr; }
            set { _ConnStr = value; }
        }

        private string _SqlCommandText;
        /// <summary></summary>
        public string SqlCommandText
        {
            get { return _SqlCommandText; }
            set { _SqlCommandText = value; }
        }

        private CommandType _SqlCommandType;
        /// <summary></summary>
        public CommandType SqlCommandType
        {
            get { return _SqlCommandType; }
            set { _SqlCommandType = value; }
        }

        private SimpleSqlParameter[] _Parameters;
        /// <summary></summary>
        public SimpleSqlParameter[] Parameters
        {
            get { return _Parameters; }
            set { _Parameters = value; }
        }

        private SqlQueryOutputType _QueryOutputType;
        /// <summary></summary>
        public SqlQueryOutputType QueryOutputType
        {
            get { return _QueryOutputType; }
            set { _QueryOutputType = value; }
        }

        private SimpleDataType _QueryScalarOutputType;
        /// <summary></summary>
        public SimpleDataType QueryScalarOutputType
        {
            get { return _QueryScalarOutputType; }
            set { _QueryScalarOutputType = value; }
        }

        private DataTable _QueryOutputTableDefinition;
        /// <summary></summary>
        public DataTable QueryOutputTableDefinition
        {
            get { return _QueryOutputTableDefinition; }
            set { _QueryOutputTableDefinition = value; }
        }
	
        #endregion
        /// <summary></summary>
        public SqlInstruction()
            : base(Guid.Empty, Guid.Empty)
        {
            this._ConnStr = string.Empty;
            this._Parameters = null;
            this._SqlCommandText = string.Empty;
            this._SqlCommandType = CommandType.Text;
            this._QueryOutputType = SqlQueryOutputType.Nothing;
            this._QueryScalarOutputType = SimpleDataType.IntNumberType;
            this._QueryOutputTableDefinition = null;
        }
        /// <summary></summary>
        public SqlInstruction(
            Guid componentID, 
            Guid jobTicketID,
            string connStr,
            string cmdTxt,
            CommandType cmdType,
            SimpleSqlParameter[] inputParameters,
            SqlQueryOutputType queryOutputType,
            SimpleDataType queryScalarOutputType,
            DataTable queryTableOutputDefinition)
            : base(componentID, jobTicketID)
        {
            this._ConnStr = connStr;
            this._SqlCommandText = cmdTxt;
            this._SqlCommandType = cmdType;
            this._Parameters = inputParameters;
            this._QueryOutputType = queryOutputType;
            this._QueryScalarOutputType = queryScalarOutputType;
            this._QueryOutputTableDefinition = queryTableOutputDefinition;
        }

        public override XmlNode Serialize(ref XmlDocument xDoc, ref XmlNode parentNode)
        {
            XmlNode xNode = XmlDataUtil.AddElement(ref xDoc, parentNode, "Instruction");
            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "Type", this.GetType().FullName);
            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "ComponentID", this.OwnerComponentID.ToString());
            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "WorkerID", this.OwnerWorkerInstanceID.ToString());
            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "SubmitTime", this.SubmitTime.ToString());
            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "StartTime", this.StartTime.ToString());

            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "ConnectionString", this.ConnStr);
            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "CommandText", this.SqlCommandText);
            XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "CommandType", ((int) this.SqlCommandType).ToString());
            XmlNode parameterRootNode = XmlDataUtil.AddElement(ref xDoc, xNode, "SqlParameters");
            if(this.Parameters !=null && this.Parameters.Length>0)
            {
                foreach (SimpleSqlParameter sqlParam in this.Parameters)
                {
                    XmlNode paramNode = XmlDataUtil.AddElement(ref xDoc, parameterRootNode, "SqlParameter");
                    XmlDataUtil.UpdateAttribute(ref xDoc, paramNode, "ParameterName", sqlParam.ParameterName);
                    XmlDataUtil.UpdateAttribute(ref xDoc, paramNode, "DataType", ((int) sqlParam.DbType).ToString());
                    XmlDataUtil.UpdateAttribute(ref xDoc, paramNode, "Direction", ((int) sqlParam.Direction).ToString());
                    XmlDataUtil.UpdateAttribute(ref xDoc, paramNode, "ParameterValue",
                                                sqlParam.Value is DBNull ? string.Empty : sqlParam.Value.ToString());
                }
            }

            XmlDataUtil.UpdateAttribute(ref xDoc,xNode,"QueryOutputType",((int)this.QueryOutputType).ToString());
            XmlDataUtil.UpdateAttribute(ref xDoc,xNode,"QueryScalarOutputType",((int)this.QueryScalarOutputType).ToString());
            if (this.QueryOutputTableDefinition != null && this.QueryOutputTableDefinition.Columns.Count > 0)
            {
                XmlNode outputTableDefNode = XmlDataUtil.AddElement(ref xDoc, xNode, "QueryTableOutputDefinition");
                foreach(DataColumn col in this.QueryOutputTableDefinition.Columns)
                {
                    XmlNode colNode = XmlDataUtil.AddElement(ref xDoc, outputTableDefNode, "Column");
                    XmlDataUtil.UpdateAttribute(ref xDoc,colNode,"ColumnName",col.ColumnName);
                    XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "DataType", col.DataType.Name);
                }
            }
            return xNode;
        }

        public override InstructionBase Instantiate(XmlNode xNode)
        {
            if (xNode.Attributes.GetNamedItem("Type").Value == this.GetType().FullName)
            {
                SqlInstruction instruction = new SqlInstruction();
                instruction.OwnerComponentID = new Guid(xNode.Attributes.GetNamedItem("ComponentID").Value);
                instruction.OwnerWorkerInstanceID = new Guid(xNode.Attributes.GetNamedItem("WorkerID").Value);
                instruction.SubmitTime = DateTime.Parse(xNode.Attributes.GetNamedItem("SubmitTime").Value);
                instruction.StartTime = DateTime.Parse(xNode.Attributes.GetNamedItem("StartTime").Value);

                instruction.ConnStr = xNode.Attributes.GetNamedItem("ConnectionString").Value;
                instruction.SqlCommandText = xNode.Attributes.GetNamedItem("CommandText").Value;
                instruction.SqlCommandType = (CommandType) int.Parse(xNode.Attributes.GetNamedItem("CommandType").Value);
                List<SimpleSqlParameter> parameters = new List<SimpleSqlParameter>();
                XmlNodeList paramNodes = xNode.SelectNodes("SqlParameters/SqlParameter");
                if(paramNodes !=null && paramNodes.Count>0)
                {
                    foreach(XmlNode paramNode in paramNodes)
                    {
                        SimpleSqlParameter sqlParam = new SimpleSqlParameter();
                        sqlParam.ParameterName = paramNode.Attributes.GetNamedItem("ParameterName").Value;
                        sqlParam.DbType = (DbType) int.Parse(paramNode.Attributes.GetNamedItem("DataType").Value);
                        sqlParam.Direction =
                            (ParameterDirection) int.Parse(paramNode.Attributes.GetNamedItem("Direction").Value);
                        string paramValue = paramNode.Attributes.GetNamedItem("ParameterValue").Value;
                        if(string.IsNullOrEmpty(paramValue))
                        {
                            sqlParam.Value = DBNull.Value;
                        }
                        else
                        {
                            sqlParam.Value = SqlDbTypeUtil.GetValueFromString(paramValue, sqlParam.DbType);
                        }
                        parameters.Add(sqlParam);
                    }
                }
                instruction.Parameters = null;
                if(parameters.Count>0)
                {
                    instruction.Parameters = parameters.ToArray();
                }

                instruction.QueryOutputType =
                    (SqlQueryOutputType) int.Parse(xNode.Attributes.GetNamedItem("QueryOutputType").Value);
                instruction.QueryScalarOutputType =
                    (SimpleDataType) int.Parse(xNode.Attributes.GetNamedItem("QueryScalarOutputType").Value);
                instruction.QueryOutputTableDefinition = null;
                XmlNodeList colNodes = xNode.SelectNodes("QueryTableOutputDefinition/Column");
                if(colNodes !=null && colNodes.Count>0)
                {
                    DataTable tbl=new DataTable();
                    foreach(XmlNode colNode in colNodes)
                    {
                        string colName = colNode.Attributes.GetNamedItem("ColumnName").Value;
                        Type colDbType = Type.GetType(colNode.Attributes.GetNamedItem("DataType").Value);
                        if(colDbType ==null || colDbType==Type.Missing.GetType())
                        {
                            colDbType = typeof (string);
                        }
                        tbl.Columns.Add(colName, colDbType);
                    }
                    instruction.QueryOutputTableDefinition = tbl;
                }

                return instruction;
            }
            else
            {
                throw new Exception("Unable to instantiate instruction: invalid type specified in xml node");
            }
        }

        public override Dictionary<string, Type> GetBindableMemberNames(Type[] excludeMemberFromTypes)
        {
            Dictionary<string, Type> bindableMembers = base.GetBindableMemberNames(excludeMemberFromTypes);
            //bindableMembers.Add("ConnStr", typeof(string));
            //bindableMembers.Add("SqlCommandText", typeof(string));
            //bindableMembers.Add("Command type",typeof(CommandType));
            //bindableMembers.Add("Query output type",typeof(SqlQueryOutputType));
            //bindableMembers.Add("Scalar output type",typeof(SimpleDataType));
            //bindableMembers.Add("Table output definition",typeof(DataTable));
            if(this.Parameters !=null && this.Parameters.Length>0)
            {
                foreach (SimpleSqlParameter sqlParam in this.Parameters)
                {
                    Type paramType = SqlDbTypeUtil.ToSystemType(sqlParam.DbType);
                    bindableMembers.Add(sqlParam.ParameterName,paramType);
                }
            }
            return bindableMembers;
        }
    }
}
